﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ResultsManagementSystem
{
    public class ScoreDao
    {
        private Score score;
        private SqlCeConnection conn;
        private SqlCeCommand cmd;
        private SqlCeDataReader rdr;

        public ScoreDao()
        {
            this.score = new Score();

            //conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn = new SqlCeConnection(@"Data Source=D:/project/rms/ResultsManagementSystem/ResultsManagementSystem/Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        public ScoreDao(Score score)
        {
            this.score = score;

            conn = new SqlCeConnection(@"Data Source=../../Results.sdf");
            conn.Open();

            cmd = conn.CreateCommand();
        }

        ~ScoreDao()
        {
            conn.Close();
        }

        public Score getScore()
        {
            return this.score;
        }

        public void setScore(Score score)
        {
            this.score = score;
        }

        public Score querryScoreByNumberAndSubjectName(String subjectName,String number)
        {
            cmd.CommandText = "SELECT * FROM " + subjectName + " where number = '" + number + "'";
            
            rdr = cmd.ExecuteReader();

            //得到成绩
            if (rdr.Read())
            {
                this.score.setId(rdr.GetInt32(0));
                this.score.setNumber(rdr.GetString(1));
                this.score.setScore(rdr.GetInt32(2));
            }
            else
            {
                this.score = null;
            }

            return this.score;

        }

        //查询某一个人的所有科目的成绩
        public List<Score> queryStudentAllScore(String number)
        {
            SubjectDao subjectDao = new SubjectDao();

            List<Subject> subjectList = subjectDao.queryAllSubject();

            List<Score> scoreList = new List<Score>();

            Score score = new Score();

            StudentSubjectDao studentSubjectDao = new StudentSubjectDao();

            Subject subject = new Subject();

            StudentDao studentDao = new StudentDao();

            StudentSubject studentSubject = new StudentSubject();

            Student student = studentDao.querryStudentByNumber(number);

            List<StudentSubject> studentSubjectList = studentSubjectDao.querryStudentSubjectByStudentId(student.getId());

            //循环查询各科成绩
            for (int i = 0; i < studentSubjectList.Count;i++ )
            {
                subject = subjectDao.querrySubjectById(studentSubjectList.ElementAt(i).getSubjectId());

                cmd.CommandText = "SELECT * FROM " + subject.getName() + " where number = '" + number + "'";

                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    scoreList.Add(new Score(rdr.GetInt32(0), rdr.GetString(1),rdr.GetInt32(2)));
                }
            }

            return scoreList;
        }

        //查询某人某一科成绩
        public Score queryScoreBySubjectNameAndStudentNumber(String subjectName, String studentNumber)
        {
            Score score = new Score();

            cmd.CommandText = "SELECT * FROM " + subjectName + " where number = '" + studentNumber + "'";

            rdr = cmd.ExecuteReader();

            if(rdr.Read())
            {
                score.setId(rdr.GetInt32(0));
                score.setNumber(rdr.GetString(1));
                score.setScore(rdr.GetInt32(2));
            }
            else
            {
                score = null;
            }

            return score;

            //

        }

        //查询某一科目的所有人的成绩
        public List<Score> querryScoreListBySubjectName(String subjectName)
        {
            List<Score> scoreList = new List<Score>();

            //判断课程是否存在
            Subject subject = new Subject();

            SubjectDao subjectDao = new SubjectDao();

            subject = subjectDao.querrySubjectByName(subjectName);

            if (subject == null)
            {
                return null;
            }
            else
            {
                cmd.CommandText = "SELECT * FROM " + subjectName;

                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    scoreList.Add(new Score(rdr.GetInt32(0), rdr.GetString(1), rdr.GetInt32(2)));
                }

                return scoreList;
            }
        }

        public bool insertScore(String table,String number,int score)
        {
            //判断是否存在相同的数据
            this.score = querryScoreByNumberAndSubjectName(table, number);

            if (this.score != null)
            {
                return false;
            }
            else
            {
                //插入数据
                cmd.CommandText = "insert into " + table + "(number,score) values('" + number + "','" + score + "')";
                cmd.ExecuteNonQuery();

                return true;
            }
        }
    }
}
